//
//  SQLiteManager.m
//  数据库OC
//
//  Created by yang on 2018/1/7.
//  Copyright © 2018年 YYY. All rights reserved.
//

//主键字段默认就包含了not null 和 unique 两个约束
//如果想要让主键自动增长（必须是integer类型），应该增加autoincrement
#import "SQLiteManager.h"
//#import "FMDatabase.h"
#import "FMDB.h"

static id instance;

@interface SQLiteManager ()
{
    FMDatabaseQueue *queue;
}

//@property(nonatomic,strong)FMDatabase *db;

@end

@implementation SQLiteManager

+ (instancetype)sharedInstance
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [[self alloc] init];
    });
    return instance;
}

+ (instancetype)allocWithZone:(struct _NSZone *)zone
{
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        instance = [super allocWithZone:zone];
    });
    return instance;
}

- (id)copyWithZone:(NSZone *)zone
{
    return instance;
}

//打开数据库
- (void)openSQLite
{
    //获取数据库文件的路径
    NSString *docPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    //数据库名
    NSString *fileName = @"homePage.sqlite";
    NSString *path = [docPath stringByAppendingPathComponent:fileName];
    NSLog(@"数据库路径：path = %@", path);
    queue = [FMDatabaseQueue databaseQueueWithPath:path];
    //创建表
    [self createTable];
    /*
     方法2
     //获得数据库
     FMDatabase *db = [FMDatabase databaseWithPath:path];
     if (![db open])
     {
     NSLog(@"数据库打开失败！");
     }
     else
     {
     NSLog(@"数据库路径：  %@",path);
     self.db = db;
     //创建表
     [self createTable];
     }
     */
}

//创建表
- (void)createTable
{
    //创建表 AUTOINCREMENT
    NSString *createSql = @"CREATE TABLE IF NOT EXISTS header(id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL, age INTEGER NOT NULL)";
    
    [queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db executeStatements:createSql]) {
            NSLog(@"创表成功！");
        }
        else
        {
            NSLog(@"创表失败！");
        }
    }];
    
    /*
     方法2
    BOOL result = [self.db executeUpdate:createSql];
    if (result)
    {
        NSLog(@"创表成功！");
        //插入数据
        [self insert];
        
        //查询数据
        [self query];
        //删除数据
        //[self delete];
    }
    else
    {
        NSLog(@"创表失败！");
    }
     */
}
//插入数据
- (void)insert
{
    NSString *insertSql = @"INSERT OR REPLACE INTO header(id, name, age) VALUES(?, ?, ?)";
    [queue inTransaction:^(FMDatabase * _Nonnull db, BOOL * _Nonnull rollback) {
        for (int i=0; i<10; i++)
        {
            NSString *name = [NSString stringWithFormat:@"jack-%u",arc4random_uniform(100)];
            if ([db executeUpdate:insertSql withArgumentsInArray:@[@(i), name, @(arc4random_uniform(40))]])
            {
                NSLog(@"插入数据成功");
            }
            else
            {
                NSLog(@"插入数据失败");
            }
        }
    }];
    /*
     方法2
    for (int i=0; i<10; i++)
    {
        NSString *name = [NSString stringWithFormat:@"jack-%u",arc4random_uniform(100)];
        //插入数据方法1
        //[self.db executeUpdate:@"INSERT INTO header(name, age) VALUES (?, ?);",name, @(arc4random_uniform(40))];
        //插入数据方法2
        //executeUpdate : 不确定的参数用?来占位
        BOOL result = [self.db executeUpdate:@"INSERT OR REPLACE INTO header(id, name, age) VALUES(?, ?, ?);" withArgumentsInArray:@[@(i), name, @(arc4random_uniform(40))]];
        if (result)
        {
            NSLog(@"插入数据成功");
        }
        else
        {
            NSLog(@"插入数据失败");
        }
        //[self.db executeUpdate:@"INSERT INTO header(name, age) VALUES(?, ?);" withArgumentsInArray:@[name, @(arc4random_uniform(40))]];
        //插入数据方法3
        //executeUpdateWithFormat : 不确定的参数用%@、%d等来占位
        //[self.db executeUpdateWithFormat:@"INSERT INTO header(name, age) VALUES(%@, %u);", name, arc4random_uniform(40)];
    }
    */
}

//删除数据
- (void)delete
{
    //删除全部数据
    NSString *deleteSql = @"DELETE FROM header";
    //删除表
    //NSString *deleteSql = @"DROP TABLE IF EXISTS header";
    
    [queue inDatabase:^(FMDatabase * _Nonnull db) {
        if ([db executeUpdate:deleteSql])
        {
            NSLog(@"删除成功");
        }
        else
        {
            NSLog(@"删除失败");
        }
    }];
    
    /*
     方法2
     BOOL result = [self.db executeUpdate:deleteSql1];
     if (result)
     {
     NSLog(@"删除成功");
     //创建表
     NSString *createSql = @"CREATE TABLE IF NOT EXISTS header(id integer PRIMARY KEY AUTOINCREMENT, name text NOT NULL, age integer NOT NULL)";
     [self.db executeUpdate:createSql];
     }
     */
}

//查询
- (void)query
{
    NSMutableArray *resultArray = [NSMutableArray array];
    //执行查询语句
    NSString *selectSql = @"SELECT * FROM header";
    [queue inDatabase:^(FMDatabase * _Nonnull db) {
        FMResultSet *resultSet = [db executeQuery:selectSql];
        while (resultSet.next)
        {
            NSInteger userID = [resultSet intForColumn:@"id"];
            NSString *name = [resultSet stringForColumn:@"name"];
            NSInteger age = [resultSet intForColumn:@"age"];
            NSString *result = [NSString stringWithFormat:@"%ld   %@    %ld", userID, name, age];
            [resultArray addObject:result];
        }
    }];
    NSLog(@"%@", resultArray);
    /*
     方法2
     FMResultSet *resultSet = [self.db executeQuery:selectSql];
     //遍历结果
     while ([resultSet next])
     {
     NSInteger userID = [resultSet intForColumn:@"id"];
     NSString *name = [resultSet stringForColumn:@"name"];
     NSInteger age = [resultSet intForColumn:@"age"];
     NSLog(@"%ld   %@    %ld", userID, name, age);
     }
     */
    
}

@end
